﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Security.Cryptography;
using System.Data;
using System.Text.RegularExpressions;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace BoletoElectronicoDesktop
{


    public static class TarjetaDAO
    {
        //devuelve la cantidad de Tarjetas que tienen ese numero
        //si no existe devuelve cero
        public static int ExisteTarjeta(string NroTarjeta)
        {

            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Tarjeta_Num_Cantidad";
            command.Parameters.AddWithValue("@numero_tarjeta", NroTarjeta);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows[0][0]);

        }
        //inserta la tarjeta en la bd
        public static void InsertarTarjeta(string NroTarjeta, string Fecha, string Cliente)
        {
            
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Tarjetas_Insercion";
            command.Parameters.AddWithValue("@numero_tarjeta", NroTarjeta);
            command.Parameters.AddWithValue("@fecha_alta ", Fecha);
            command.Parameters.AddWithValue("@cliente", Cliente);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();

        }

        
        //filtra los clientes que cumplen la condicion
        
        public static DataTable FiltrarClientesEnTarjeta(string apellido, string nombre, string tipodoc, string numdoc, string mail, string tel, string calle, string num, string piso, string depto)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Cliente_Filtro";
            if (string.Compare(apellido, "") != 0)
                command.Parameters.AddWithValue("@apellido", apellido);
            if (string.Compare(nombre, "") != 0)
                command.Parameters.AddWithValue("@nombre", nombre);
            if (string.Compare(tipodoc, "") != 0)
                command.Parameters.AddWithValue("@tipo_documento", tipodoc);
            if (string.Compare(numdoc, "") != 0)
                command.Parameters.AddWithValue("@numero_documento", numdoc);
            if (string.Compare(mail, "") != 0)
                command.Parameters.AddWithValue("@mail", mail);
            if (string.Compare(tel, "") != 0)
                command.Parameters.AddWithValue("@tel", tel);
            if (string.Compare(calle, "") != 0)
                command.Parameters.AddWithValue("@calle", calle);
            if (string.Compare(num, "") != 0)
                command.Parameters.AddWithValue("@numero_calle", num);
            if (string.Compare(depto, "") != 0)
                command.Parameters.AddWithValue("@depto", depto);
            if (string.Compare(piso, "") != 0)
                command.Parameters.AddWithValue("@piso", piso);
               
            //agrego q el cliente tiene q estar habilitado para q se le pueda asignar una tarjeta
            command.Parameters.AddWithValue("@habilitado", "True");
           
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;
        }

        public static int ClienteTieneTarjeta(string idCliente)
        {

            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].ClientesConTarjeta_Cantidad";
            command.Parameters.AddWithValue("@idCliente", idCliente);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows[0][0]);

        }
        public static DataTable FiltrarTarjetasHabilitadas(string nroTarjeta, string fecha, string cliente)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Tarjetas_Filtro";
            if (string.Compare(nroTarjeta, "") != 0)
                command.Parameters.AddWithValue("@numero_tarjeta", nroTarjeta);
            if (string.Compare(cliente, "") != 0)
                command.Parameters.AddWithValue("@cliente", cliente);
            if (string.Compare(fecha, "") != 0)
                command.Parameters.AddWithValue("@fecha_alta", fecha);
            //agrego q la tarjeta tiene que estar habilitada
            command.Parameters.AddWithValue("@habilitado", "True");

            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;

        }
        //da de baja una tarjeta
        public static void InhabilitarTarjeta(string NroTarjeta)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Tarjeta_Dar_Baja";
            command.Parameters.AddWithValue("@numero_tarjeta", NroTarjeta);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
        }
        //filtra todas las tarjetas, habilitadas o no
        public static DataTable FiltrarTarjetas(string nroTarjeta, string fecha, string cliente)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Tarjetas_Filtro";
            if (string.Compare(nroTarjeta, "") != 0)
                command.Parameters.AddWithValue("@numero_tarjeta", nroTarjeta);
            if (string.Compare(cliente, "") != 0)
                command.Parameters.AddWithValue("@cliente", cliente);
            if (string.Compare(fecha, "") != 0)
                command.Parameters.AddWithValue("@fecha_alta", fecha);

            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;

        }

        //devuelve la cantidad de tarjetas diferente a si misma
        public static int ExisteTarjetaDiferente(string NroTarjeta, string numeroViejo)
        {

            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Tarjeta_Diferente_Cantidad";
            command.Parameters.AddWithValue("@numero_tarjeta", NroTarjeta);
            command.Parameters.AddWithValue("@numeroViejo", numeroViejo);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows[0][0]);
        }
        //devuelve la cantidad de clientes que tienen una tarjeta
        public static int ExisteClienteDiferente(string idCliente, string clienteAnterior)
        {

            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Tarjeta_ClientesDiferentes_Cantidad";
            command.Parameters.AddWithValue("@idCliente", idCliente);
            command.Parameters.AddWithValue("@idClienteViejo", clienteAnterior);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows[0][0]);

        }
        public static void ModificarTarjeta(string numTarjeta, string numTarjetaViejo, string cliente, string fecha, string habilitado)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Tarjetas_Modificacion]";
            command.Parameters.AddWithValue("@numero_tarjeta", numTarjeta);
            command.Parameters.AddWithValue("@numero_viejo", numTarjetaViejo);
            command.Parameters.AddWithValue("@cliente", cliente);
            command.Parameters.AddWithValue("@fecha", fecha);
            command.Parameters.AddWithValue("@habilitado", habilitado);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
        }
        //devuelve las tarjetas premium para un año
        public static DataTable ObtenerTarjetasPremium(string año)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Clientes_Premium]";
            command.Parameters.AddWithValue("@año", año);
             command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;
        }

        public static DataTable ObtenerTarjetasAInhabilitar()
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Tarjetas_Inactivas]";
            command.CommandTimeout = 300;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;
        }
    } // fin clase TarjetaDAO











}